""" This is class for connecting and inserting data into data server"""
import os
import sys
import psycopg2
import gdal
import numpy as np
import datetime
from utilities import __get_bounding_coor__
from utilities import __get_log_time_format__
from utilities import __get_other_filename__
from gdalconst import *
from constants import __HOME_DIR_DATA__
from constants import __HOME_DIR_RESAMPLE_DATA__

# convert from file name to timestamp
def __convert_to_timestamp__(filename):
	year = filename[17:21]
	month = filename[21:23]
	day = filename[23:25]
	hour = filename[36:38]
	minute = filename[38:40]
	second = filename[40:42]
	times = year + "-" + month + "-" + day + " " + hour + ":" + minute + ":" + second
	return times

# class server to connect and manipulate data 
class Server:
	
	# init
	def __init__(self, host="192.168.3.4", dbname="fimo", user="rasdaman", passwd="rasdaman", port=5432 ):
		self.con = None
		self.host = host
		self.dbname = dbname
		self.user = user
		self.passwd = passwd
		self.port = port
		self.cur = None 

	# connect to server
	def __connect__(self, log):
		try:
			flog = open(log, "a")
			self.con = psycopg2.connect(database=self.dbname, user=self.user, password=self.passwd, host=self.host, port=self.port)	
			self.cur = self.con.cursor()
			flog.write("[{0}] connect to database server successfully\n".format(__get_log_time_format__()))
			flog.close()
			return 1
		except psycopg2.Error as e:
			flog.write("[{0}] can't connect to database server: {1}\n".format(__get_log_time_format__(), str(e)))
			flog.close()
			return 0
	
	# get the nesrest downloaded day and current day from database
	def __get_nearest_day__(self):
		nearest_day = 0
		download_day = 0
		sql_n = "SELECT TO_CHAR(aqstime, 'J')::integer FROM apom.satorgviirsaot ORDER BY AGE(NOW(), aqstime) ASC LIMIT 1"
		sql_d = "select TO_CHAR(timestamp 'now()', 'J')::integer - 5"
		try:
			self.cur.execute(sql_n)
			nearest_day = self.cur.fetchone()
			self.cur.execute(sql_d)
			download_day = self.cur.fetchone()
			return int(nearest_day[0]), int(download_day[0])
		except psycopg2.Error as e:
			return 0, 0

	# insert data into database
	def __insert_data__(self, log, directory):
		flog = open(log, "a")
		h5files = [f for f in os.listdir(directory) if os.path.isfile(f) and f.endswith('.h5')]
		for f in h5files:
			resamp = __get_other_filename__(f, directory, ".tif")
			if resamp == "":
				continue
			lad = "HDF5:" + f + "://All_Data/VIIRS-Aeros-EDR-GEO_All/Latitude"
			lod = "HDF5:" + f + "://All_Data/VIIRS-Aeros-EDR-GEO_All/Longitude"
			coors = __get_bounding_coor__(lad, lod)
			times = __convert_to_timestamp__(f)
			path = __HOME_DIR_DATA__ + f[17:21] + "/" + f[:-3] + "/"
			t = datetime.datetime.now().replace(tzinfo=None)
			in_time = t.strftime('%Y-%m-%d %H:%M:%S')
			sql = "INSERT INTO apom.satorgviirsaot(typeid, aqstime, filename, path, corner) VALUES(1, '{0}', '{1}', '{2}', ST_GeomFromText('POLYGON(( {3} {4}, {5} {6}, {7} {8}, {9} {10}, {11} {12}))', 4326))".format(times, f, path, str(coors[0]), str(coors[3]), str(coors[0]), str(coors[2]), str(coors[1]), str(coors[3]), str(coors[1]), str(coors[2]), str(coors[0]), str(coors[3])) 
			try:
				self.cur.execute(sql)
				self.con.commit()
				flog.write("[{0}] successfully inserted information of {1} into database\n".format(__get_log_time_format__(), f))
			except psycopg2.Error as e:
				flog.write("[{0}] can't insert {1} to database server: {2}\n".format(__get_log_time_format__(), f, str(e)))
				return 0	
		flog.close()
		return 1

	# delete data from database
	def __delete_data__(self, log, directory):
		flog = open(log, "a")
		h5file = [f for f in os.listdir(directory) if os.path.isfile(f) and f.endswith('.h5')]
		for f in h5file:
			sql = "delete from apom.satorgviirsaot where filename='{0}'".format(f)
			try:
				self.cur.execute(sql)
				self.con.commit()
				flog.write("[{0}] deleted information of {1} from apom.satorgviirsaot\n".format(__get_log_time_format__(), f))
			except psycopg2.Error as e:
				return 0
		flog.close()
		return 1

	# insert resample image
	def __insert_resample_data__(self, log, directory):
		flog = open(log, "a")
		tiffiles = [f for f in os.listdir(directory) if os.path.isfile(f) and f.endswith('.tif')]
		if not os.path.exists("raster2pgsql"): os.makedirs("raster2pgsql")
		for f in tiffiles:
			os.system("raster2pgsql -a -f rasref6 -F {0} apom.satresampviirsaot > raster2pgsql/{1}.sql ".format(f, f))
		WORKDIR = "raster2pgsql/"
		os.chdir(WORKDIR)
		sqlfiles = [f for f in os.listdir(".") if os.path.isfile(f) and f.endswith('.sql')]
		check = 1
		for f in sqlfiles:
    			check = os.system("psql -q -d " + "\"dbname=fimo user=rasdaman password=rasdaman host=192.168.3.4 port=5432\"" + " -f " + f )
		os.chdir("../")
		if check != 0:
			return 0
		flog.write("[{0}] successfully inserted resample data into database\n".format(__get_log_time_format__()))
		flog.close()
		return 1

	# update information image
	def __update_information_image__(self, log, directory):
		flog = open(log, "a")
		tiffiles = [f for f in os.listdir(directory) if os.path.isfile(f) and f.endswith('.tif')]
		for f in tiffiles:
			aqstime = __convert_to_timestamp__(f)
			path = __HOME_DIR_RESAMPLE_DATA__ + f[17:21] + "/" + f[:-4] + "/"
			t = datetime.datetime.now().replace(tzinfo=None)
			in_time = t.strftime('%Y-%m-%d %H:%M:%S')
			sql = "UPDATE apom.satresampviirsaot SET gridid=6000, aqstime='{0}', filepath='{1}', projection=4326 WHERE filename='{2}'".format(aqstime, path, f)
			try:
				self.cur.execute(sql)
				self.con.commit()
				flog.write("[{0}] updated information of {1} into database\n".format(__get_log_time_format__(), f))
			except psycopg2.Error as e:
				flog.write("[{0}] can't update {1} to database server: {2}\n".format(__get_log_time_format__(), f, str(e)))
				return 0
		flog.close()
		return 1
		
	# do everything
	def __do_everything__(self, log, directory):
		r = 0
		s = 0
		t = 0
		r = self.__insert_data__(log, directory)
		if r == 1:
			s = self.__insert_resample_data__(log, directory)
		if s == 0:
			self.__delete_data__(log, directory)	
		else:
			t = self.__update_information_image__(log, directory)
		if t == 1:
			return 1
		return 0

	# disconnect
	def __disconnect__(self):
		if self.con:
			self.con.close()

